<?xml version="1.0" encoding="UTF-8" ?>
<!--

    Copyright Camunda Services GmbH and/or licensed to Camunda Services GmbH
    under one or more contributor license agreements. See the NOTICE file
    distributed with this work for additional information regarding copyright
    ownership. Camunda licenses this file to you under the Apache License,
    Version 2.0; you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

        http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.

-->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.camunda.bpm.engine.impl.persistence.entity.ExternalTaskEntity">

  <resultMap id="externalTaskResultMap" type="org.camunda.bpm.engine.impl.persistence.entity.ExternalTaskEntity">
    <id property="id" column="ID_" jdbcType="VARCHAR"/>
    <result property="revision" column="REV_" jdbcType="INTEGER"/>
    <result property="topicName" column="TOPIC_NAME_" jdbcType="VARCHAR"/>
    <result property="workerId" column="WORKER_ID_" jdbcType="VARCHAR" />
    <result property="retries" column="RETRIES_" jdbcType="INTEGER" />
    <result property="errorMessage" column="ERROR_MSG_" jdbcType="VARCHAR"/>
    <result property="errorDetailsByteArrayId" column="ERROR_DETAILS_ID_" jdbcType="VARCHAR" />

    <result property="lockExpirationTime" column="LOCK_EXP_TIME_" jdbcType="TIMESTAMP"/>
    <result property="suspensionState" column="SUSPENSION_STATE_" jdbcType="INTEGER" />
    <result property="executionId" column="EXECUTION_ID_" jdbcType="VARCHAR" />
    <result property="processInstanceId" column="PROC_INST_ID_" jdbcType="VARCHAR" />
    <result property="processDefinitionId" column="PROC_DEF_ID_" jdbcType="VARCHAR" />
    <result property="processDefinitionKey" column="PROC_DEF_KEY_" jdbcType="VARCHAR" />
    <result property="processDefinitionVersionTag" column="VERSION_TAG_" jdbcType="VARCHAR" />
    <result property="activityId" column="ACT_ID_" jdbcType="VARCHAR" />
    <result property="activityInstanceId" column="ACT_INST_ID_" jdbcType="VARCHAR" />
    <result property="tenantId" column="TENANT_ID_" jdbcType="VARCHAR" />
    <result property="priority" column="PRIORITY_" jdbcType="BIGINT" />
    <result property="businessKey" column="BUSINESS_KEY_" jdbcType="VARCHAR" />
    <result property="lastFailureLogId" column="LAST_FAILURE_LOG_ID_" jdbcType="VARCHAR" />
    <!-- note: if you add mappings here, make sure to select the columns in 'columnSelection' -->
  </resultMap>
  
  <resultMap type="org.camunda.bpm.engine.impl.util.ImmutablePair" id="deploymentIdMapping">
    <id property="left" column="DEPLOYMENT_ID_" jdbcType="VARCHAR" />
    <id property="right" column="ID_" jdbcType="VARCHAR" />
  </resultMap>

  <insert id="insertExternalTask" parameterType="org.camunda.bpm.engine.impl.persistence.entity.ExternalTaskEntity">
    insert into ${prefix}ACT_RU_EXT_TASK (
      ID_,
      WORKER_ID_,
      TOPIC_NAME_,
      LOCK_EXP_TIME_,
      RETRIES_,
      ERROR_MSG_,
      ERROR_DETAILS_ID_,
      SUSPENSION_STATE_,
      EXECUTION_ID_,
      PROC_INST_ID_,
      PROC_DEF_ID_,
      PROC_DEF_KEY_,
      ACT_ID_,
      ACT_INST_ID_,
      TENANT_ID_,
      PRIORITY_,
      LAST_FAILURE_LOG_ID_,
      REV_
    ) values (
      #{id, jdbcType=VARCHAR},
      #{workerId, jdbcType=VARCHAR},
      #{topicName, jdbcType=VARCHAR},
      #{lockExpirationTime, jdbcType=TIMESTAMP},
      #{retries, jdbcType=INTEGER},
      #{errorMessage, jdbcType=VARCHAR},
      #{errorDetailsByteArrayId, jdbcType=VARCHAR},
      #{suspensionState, jdbcType=INTEGER},
      #{executionId, jdbcType=VARCHAR},
      #{processInstanceId, jdbcType=VARCHAR},
      #{processDefinitionId, jdbcType=VARCHAR},
      #{processDefinitionKey, jdbcType=VARCHAR},
      #{activityId, jdbcType=VARCHAR},
      #{activityInstanceId, jdbcType=VARCHAR},
      #{tenantId, jdbcType=VARCHAR},
      #{priority, jdbcType=BIGINT},
      #{lastFailureLogId, jdbcType=VARCHAR},
      1
    )
  </insert>
  
  <update id="updateExternalTask" parameterType="org.camunda.bpm.engine.impl.persistence.entity.ExternalTaskEntity">
    update ${prefix}ACT_RU_EXT_TASK
    <set>
      REV_ = #{revisionNext, jdbcType=INTEGER},
      WORKER_ID_ = #{workerId, jdbcType=VARCHAR},
      TOPIC_NAME_ = #{topicName, jdbcType=VARCHAR},
      LOCK_EXP_TIME_ = #{lockExpirationTime, jdbcType=TIMESTAMP},
      RETRIES_ = #{retries, jdbcType=INTEGER},
      ERROR_MSG_ = #{errorMessage, jdbcType=VARCHAR},
      ERROR_DETAILS_ID_ = #{errorDetailsByteArrayId, jdbcType=VARCHAR},
      EXECUTION_ID_ = #{executionId, jdbcType=VARCHAR},
      PROC_INST_ID_ = #{processInstanceId, jdbcType=VARCHAR},
      PROC_DEF_ID_ = #{processDefinitionId, jdbcType=VARCHAR},
      PROC_DEF_KEY_ = #{processDefinitionKey, jdbcType=VARCHAR},
      ACT_ID_ = #{activityId, jdbcType=VARCHAR},
      ACT_INST_ID_ = #{activityInstanceId, jdbcType=VARCHAR},
      SUSPENSION_STATE_ = #{suspensionState, jdbcType=INTEGER},
      PRIORITY_ = #{priority, jdbcType=BIGINT},
      LAST_FAILURE_LOG_ID_ = #{lastFailureLogId, jdbcType=VARCHAR}
    </set>
    where ID_= #{id, jdbcType=VARCHAR}
      and REV_ = #{revision, jdbcType=INTEGER}
  </update>
  
  <update id="updateExternalTaskSuspensionStateByParameters" parameterType="org.camunda.bpm.engine.impl.db.ListQueryParameterObject">
    update ${prefix}ACT_RU_EXT_TASK
    <set>
      SUSPENSION_STATE_ = #{parameter.suspensionState, jdbcType=INTEGER}
    </set>
    <where>
      <if test="parameter.processInstanceId != null">
        PROC_INST_ID_ = #{parameter.processInstanceId}
      </if>
      <if test="parameter.processDefinitionId != null">
        and PROC_DEF_ID_ = #{parameter.processDefinitionId}
      </if>
      <if test="parameter.processDefinitionKey != null">
        <if test="!parameter.isProcessDefinitionTenantIdSet">
          and PROC_DEF_KEY_ = #{parameter.processDefinitionKey, jdbcType=VARCHAR}
        </if>
        <if test="parameter.isProcessDefinitionTenantIdSet">
          and PROC_DEF_ID_ IN (
            SELECT ID_ 
            FROM ${prefix}ACT_RE_PROCDEF PD
            WHERE PD.KEY_ = #{parameter.processDefinitionKey, jdbcType=VARCHAR}
            <if test="parameter.processDefinitionTenantId != null">
              and PD.TENANT_ID_ = #{parameter.processDefinitionTenantId, jdbcType=VARCHAR}
            </if>
            <if test="parameter.processDefinitionTenantId == null">
              and PD.TENANT_ID_ is null
            </if>
          )
        </if>
        <bind name="columnPrefix" value="''"/>
        <include refid="org.camunda.bpm.engine.impl.persistence.entity.TenantEntity.queryTenantCheckWithPrefix" />
      </if>
    </where>
  </update>
  
  <delete id="deleteExternalTask" parameterType="org.camunda.bpm.engine.impl.persistence.entity.ExternalTaskEntity">
    delete from ${prefix}ACT_RU_EXT_TASK where ID_ = #{id} and REV_ = #{revision}
  </delete>
  
  <select id="selectExternalTask" parameterType="string" resultMap="externalTaskResultMap">
   select * from ${prefix}ACT_RU_EXT_TASK where ID_ = #{id, jdbcType=VARCHAR}
  </select>
  
  <select id="selectExternalTasksByExecutionId" parameterType="org.camunda.bpm.engine.impl.db.ListQueryParameterObject" resultMap="externalTaskResultMap">
    select * from ${prefix}ACT_RU_EXT_TASK where EXECUTION_ID_ = #{parameter, jdbcType=VARCHAR}
  </select>
  
  <select id="selectExternalTasksByProcessInstanceId" parameterType="org.camunda.bpm.engine.impl.db.ListQueryParameterObject" resultMap="externalTaskResultMap">
    select * from ${prefix}ACT_RU_EXT_TASK where PROC_INST_ID_ = #{parameter, jdbcType=VARCHAR}
  </select>
  
  <select id="selectExternalTasksForTopics" parameterType="org.camunda.bpm.engine.impl.db.ListQueryParameterObject" resultMap="externalTaskResultMap">
    <bind name="orderingProperties" value="parameter.orderingProperties" />
    <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.bindOrderBy"/>

    <bind name="performAuthorizationCheck" value="authCheck.shouldPerformAuthorizatioCheck &amp;&amp; !authCheck.revokeAuthorizationCheckEnabled &amp;&amp; authCheck.authUserId != null" />

    ${limitBefore}
    select 
    <!-- Distinct can only be omitted when there are no joins with a 1:n relationship.
      Currently this is only the case for the authorization joins. When you add a join
      in this statement in the future, check if distinct can be omitted or not -->
    <if test="!parameter.usesPostgres || performAuthorizationCheck">
      ${distinct} 
    </if>
    RES.*
    ${limitBetween}
    from (
    select RES.*, PI.BUSINESS_KEY_, PD.VERSION_TAG_
    from ${prefix}ACT_RU_EXT_TASK RES

    left join ${prefix}ACT_RU_EXECUTION PI on RES.PROC_INST_ID_ = PI.ID_
    inner join ${prefix}ACT_RE_PROCDEF PD on RES.PROC_DEF_ID_ = PD.ID_

    <if test="performAuthorizationCheck">
      <include refid="org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.authCheckJoinWithoutOnClause" /> 
      AUTH ON (
       (AUTH.RESOURCE_TYPE_ = 8 
        AND (AUTH.RESOURCE_ID_ ${authJoinStart} RES.PROC_INST_ID_ ${authJoinSeparator} '*' ${authJoinEnd})   
        AND ${bitand1}AUTH.PERMS_${bitand2}2${bitand3} = 2)
      OR
       (AUTH.RESOURCE_TYPE_ = 6 
        AND (AUTH.RESOURCE_ID_ ${authJoinStart} RES.PROC_DEF_KEY_ ${authJoinSeparator} '*' ${authJoinEnd}) 
        AND ${bitand1}AUTH.PERMS_${bitand2}512${bitand3} = 512) 
      )
      <include refid="org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.authCheckJoinWithoutOnClause" />
      AUTH1 ON (
         (AUTH1.RESOURCE_TYPE_ = 8 
          AND (AUTH1.RESOURCE_ID_ ${authJoin1Start} RES.PROC_INST_ID_ ${authJoin1Separator} '*' ${authJoin1End})
          AND ${bitand1}AUTH1.PERMS_${bitand2}4${bitand3} = 4)         
      OR
         (AUTH1.RESOURCE_TYPE_ = 6 
          AND (AUTH1.RESOURCE_ID_ ${authJoin1Start} RES.PROC_DEF_KEY_ ${authJoin1Separator} '*' ${authJoin1End}) 
          AND ${bitand1}AUTH1.PERMS_${bitand2}1024${bitand3} = 1024)
      )    
    </if>
    <where>
      (RES.LOCK_EXP_TIME_ is null or RES.LOCK_EXP_TIME_ &lt;= #{parameter.now, jdbcType=TIMESTAMP})
      and (RES.SUSPENSION_STATE_ is null or RES.SUSPENSION_STATE_ = 1)
      and (RES.RETRIES_ is null or RES.RETRIES_ > 0)
      <if test="parameter != null &amp;&amp; parameter.topics.size > 0">
        and
        <foreach collection="parameter.topics" open="(" close=")" separator="or" item="topicFilters">
          RES.TOPIC_NAME_ = #{topicFilters.topicName}
          <if test="topicFilters.businessKey">
            and PI.BUSINESS_KEY_ = #{topicFilters.businessKey}
          </if>
          <if test="topicFilters.processDefinitionId != null">
            and RES.PROC_DEF_ID_ = #{topicFilters.processDefinitionId}
          </if>
          <if test="topicFilters.processDefinitionIds != null &amp;&amp; topicFilters.processDefinitionIds.length > 0">
            and RES.PROC_DEF_ID_ in
            <foreach item="item" index="index" collection="topicFilters.processDefinitionIds"
                     open="(" separator="," close=")">
              #{item}
            </foreach>
          </if>
          <if test="topicFilters.processDefinitionKey != null">
            and RES.PROC_DEF_KEY_ = #{topicFilters.processDefinitionKey}
          </if>
          <if test="topicFilters.processDefinitionKeys != null &amp;&amp; topicFilters.processDefinitionKeys.length > 0">
            and RES.PROC_DEF_KEY_ in
            <foreach item="item" index="index" collection="topicFilters.processDefinitionKeys"
                     open="(" separator="," close=")">
              #{item}
            </foreach>
          </if>
          <if test="topicFilters.isTenantIdSet">
            <if test="topicFilters.tenantIds == null" >
              and RES.TENANT_ID_ is null
            </if>
            <if test="topicFilters.tenantIds != null &amp;&amp; topicFilters.tenantIds.length > 0">
              and RES.TENANT_ID_ in
              <foreach item="tenantId" index="index" collection="topicFilters.tenantIds"
                       open="(" separator="," close=")">
                #{tenantId}
              </foreach>
            </if>
          </if>
          <if test="topicFilters.filterVariables.size > 0">
            and RES.PROC_INST_ID_ in (
              select VAR.EXECUTION_ID_
              from ${prefix}ACT_RU_VARIABLE VAR
              WHERE
              <bind name="varPrefix" value="'VAR.'"/>
              <bind name="varTypeField" value="'TYPE_'"/>
              <foreach collection="topicFilters.filterVariables" open="(" close=")" separator="or" item="variable">
                ${varPrefix}NAME_ = #{variable.name}
                and
                <bind name="queryVariableValue" value="variable" />
                <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.variableValueConditions" />
              </foreach>
              )
          </if>
          <if test="topicFilters.processDefinitionVersionTag != null">
            and (PD.VERSION_TAG_ like #{topicFilters.processDefinitionVersionTag})
          </if>
        </foreach>

      </if>
    </where>
    <include refid="org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.queryAuthorizationCheck" />
    <include refid="org.camunda.bpm.engine.impl.persistence.entity.TenantEntity.queryTenantCheck" />
    ) RES
    <if test="parameter.applyOrdering">
      ${orderBy}
    </if>
    ${limitAfter}
  </select>

  <select id="selectTopicNamesByQuery" parameterType="org.camunda.bpm.engine.impl.ExternalTaskQueryImpl" resultType="string">
    select distinct RES.TOPIC_NAME_
    <include refid="selectExternalTaskByQueryCriteriaSql"/>
  </select>

  <select id="selectExternalTaskByQueryCriteria" parameterType="org.camunda.bpm.engine.impl.ExternalTaskQueryImpl" resultMap="externalTaskResultMap">
    <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.bindOrderBy"/>
    ${limitBefore}
    select ${distinct}
    <include refid="columnSelection"/>
    ${limitBetween}
    <include refid="selectExternalTaskByQueryCriteriaSql"/>
    ${orderBy}
    ${limitAfter}
  </select>

  <select id="selectExternalTaskIdsByQueryCriteria" parameterType="org.camunda.bpm.engine.impl.ExternalTaskQueryImpl" resultType="string">
    <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.bindOrderBy"/>
    ${limitBefore}
    select ${distinct} RES.ID_
    ${limitBetween}
    <include refid="selectExternalTaskByQueryCriteriaSql"/>
    ${orderBy}
    ${limitAfter}
  </select>

  <select id="selectExternalTaskCountByQueryCriteria" parameterType="org.camunda.bpm.engine.impl.ExternalTaskQueryImpl" resultType="long">
    ${countDistinctBeforeStart} RES.ID_ ${countDistinctBeforeEnd}
    <include refid="selectExternalTaskByQueryCriteriaSql"/>
    ${countDistinctAfterEnd}
  </select>
  
  <select id="selectExternalTaskDeploymentIdMappingsByQueryCriteria" parameterType="org.camunda.bpm.engine.impl.ExternalTaskQueryImpl" resultMap="deploymentIdMapping">
    select distinct PD.DEPLOYMENT_ID_, RES.ID_
    <include refid="selectExternalTaskByQueryCriteriaSql"/>
    inner join ${prefix}ACT_RE_PROCDEF PD on RES.PROC_DEF_ID_ = PD.ID_
    ORDER BY PD.DEPLOYMENT_ID_ ASC
  </select>

  <sql id="selectExternalTaskByQueryCriteriaSql">
    from (
    select RES.*, PI.BUSINESS_KEY_, PD.VERSION_TAG_
    from ${prefix}ACT_RU_EXT_TASK RES
    
    left join ${prefix}ACT_RU_EXECUTION PI on RES.PROC_INST_ID_ = PI.ID_
    left join ${prefix}ACT_RE_PROCDEF PD on RES.PROC_DEF_ID_ = PD.ID_

    <if test="authCheck.shouldPerformAuthorizatioCheck &amp;&amp; !authCheck.revokeAuthorizationCheckEnabled &amp;&amp; authCheck.authUserId != null">
      <include refid="org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.authCheckJoinWithoutOnClause" /> 
      AUTH ON (
       (AUTH.RESOURCE_TYPE_ = 8 
        AND AUTH.RESOURCE_ID_ ${authJoinStart} RES.PROC_INST_ID_ ${authJoinSeparator} '*' ${authJoinEnd} 
        AND ${bitand1}AUTH.PERMS_${bitand2}2${bitand3} = 2)
      OR
       (AUTH.RESOURCE_TYPE_ = 6
        AND AUTH.RESOURCE_ID_ ${authJoinStart} RES.PROC_DEF_KEY_ ${authJoinSeparator} '*' ${authJoinEnd} 
        AND ${bitand1}AUTH.PERMS_${bitand2}512${bitand3} = 512) 
      )
    </if>
    <where>
      <if test="externalTaskId != null">
        and RES.ID_ = #{externalTaskId}
      </if>
      <if test="externalTaskIds != null and !externalTaskIds.isEmpty()">
        and
        <bind name="listOfIds" value="externalTaskIds"/>
        <bind name="fieldName" value="'RES.ID_'"/>
        <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.applyInForPaginatedCollection"/>
      </if>
      <if test="workerId != null">
        and RES.WORKER_ID_ = #{workerId}
      </if>
      <if test="lockExpirationBefore != null">
        and RES.LOCK_EXP_TIME_ &lt; #{lockExpirationBefore}
      </if>
      <if test="lockExpirationAfter != null">
        and RES.LOCK_EXP_TIME_ &gt; #{lockExpirationAfter}
      </if>
      <if test="topicName != null">
        and RES.TOPIC_NAME_ = #{topicName}
      </if>
      <if test="locked != null &amp;&amp; locked">
        and RES.LOCK_EXP_TIME_ is not null
        and RES.LOCK_EXP_TIME_ &gt; #{now, jdbcType=TIMESTAMP}
      </if>
      <if test="notLocked != null &amp;&amp; notLocked">
        and (RES.LOCK_EXP_TIME_ is null
        or RES.LOCK_EXP_TIME_ &lt; #{now, jdbcType=TIMESTAMP})
      </if>
      <if test="executionId != null">
        and RES.EXECUTION_ID_ = #{executionId}
      </if>
      <if test="processInstanceId != null">
        and RES.PROC_INST_ID_ = #{processInstanceId}
      </if>
      <if test="processInstanceIdIn != null &amp;&amp; processInstanceIdIn.length > 0">
        and
        <bind name="listOfIds" value="processInstanceIdIn"/>
        <bind name="fieldName" value="'RES.PROC_INST_ID_'"/>
        <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.applyInForPaginatedArray"/>
      </if>
      <if test="processDefinitionId != null">
        and RES.PROC_DEF_ID_ = #{processDefinitionId}
      </if>
      <if test="activityId != null">
        and RES.ACT_ID_ = #{activityId}
      </if>
      <if test="activityIdIn != null &amp;&amp; activityIdIn.length > 0">
        and RES.ACT_ID_ in
        <foreach item="activityId" index="index" collection="activityIdIn"
                 open="(" separator="," close=")">
          #{activityId}
        </foreach>
      </if>
      <if test="retriesLeft != null &amp;&amp; retriesLeft">
        and (RES.RETRIES_ is null
        or RES.RETRIES_ &gt; 0)
      </if>
      <if test="retriesLeft != null &amp;&amp; !retriesLeft">
        and RES.RETRIES_ is not null
        and RES.RETRIES_ = 0
      </if>
      <if test="suspensionState != null">
        <if test="suspensionState.stateCode == 1">
            and RES.SUSPENSION_STATE_ = 1
        </if>
        <if test="suspensionState.stateCode == 2">
            and RES.SUSPENSION_STATE_ = 2
        </if>
      </if>
      <if test="priorityHigherThanOrEquals != null">
        and RES.PRIORITY_ &gt;= #{priorityHigherThanOrEquals}
      </if>
      <if test="priorityLowerThanOrEquals != null">
        and RES.PRIORITY_ &lt;= #{priorityLowerThanOrEquals}
      </if>
      <if test="tenantIds != null &amp;&amp; tenantIds.length > 0">
        and RES.TENANT_ID_ in
        <foreach item="tenantId" index="index" collection="tenantIds"
                 open="(" separator="," close=")">
          #{tenantId}
        </foreach>
      </if>
      <include refid="org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.queryAuthorizationCheck" />
      <include refid="org.camunda.bpm.engine.impl.persistence.entity.TenantEntity.queryTenantCheck" />
    </where>
    ) RES
  </sql>
  
  <sql id="columnSelection">
    RES.REV_, 
    RES.ID_, 
    RES.TOPIC_NAME_, 
    RES.WORKER_ID_, 
    RES.LOCK_EXP_TIME_,
    RES.RETRIES_,
    RES.ERROR_MSG_,
    RES.ERROR_DETAILS_ID_,
    RES.EXECUTION_ID_,
    RES.PROC_INST_ID_,
    RES.PROC_DEF_ID_,
    RES.PROC_DEF_KEY_,
    RES.ACT_ID_,
    RES.ACT_INST_ID_,
    RES.SUSPENSION_STATE_,
    RES.TENANT_ID_,
    RES.PRIORITY_,
    RES.BUSINESS_KEY_,
    RES.VERSION_TAG_,
    RES.LAST_FAILURE_LOG_ID_
  </sql>

</mapper>